<?php

class ExcelController extends BaseController {


	//agen
	public function getExcelagen()
	{	
		$b = Session::get('bulan');
		$t = Session::get('tahun');
        $bln = array('Januari' => 1, 'Februari' => 2, 'Maret' => 3, 'April' => 4, 'Mei' => 5, 'Juni' => 6, 'Juli' => 7, 'Agustus' => 8, 'September' => 9 , 'Oktober' => 10, 'November' => 11, 'Desember' => 12 );
		foreach($bln as $key => $val){ if($val == $b) $bulan = $key; };
		$name = "Daftar agen";
		$title = "Daftar agen bulan ".$bulan." ".$t;
		$no = 1;
		$dataagen = DB::table('sys_agen')
					->join('sys_member','member_agen_id','=','agen_id')
					->join('ref_provinsi','member_provinsi_id','=','provinsi_id')
					->join('ref_regional','member_regional_id','=','regional_id')
	  				->whereBetween('member_joindate', array($t.'-'.$b.'-01 00:00:00', $t.'-'.$b.'-31 23:59:59'))
					->get();
		

		Excel::create('Laporan bulanan', function($excel) use($name,$dataagen,$no) {

		    $excel->sheet($name, function($sheet) use($dataagen,$no) {
		    						$sheet->row(1, array(
												 "No",
										         "Kode agen",
										         "Nama agen",
										         "Referal",
										         "No telefon",
										      	 "Tanggal gabung",
										      	 "Status"
									));
						    	foreach($dataagen as $agen){
									$refc = DB::table('sys_agen')
						                  ->where('agen_id',$agen->agen_sponsor_id) 
						                  ->count();
						            $ref = DB::table('sys_agen')
						                  ->where('agen_id',$agen->agen_sponsor_id) 
						                  ->first();
						            if($refc==0){
						                $code = 'Perusahaan';
						            }else{
						                $code = $ref->agen_code;
						            }
									
									$sheet->row($no+1, array(
										$no,
										$agen->agen_code,
										$agen->member_name,
										$code,
										$agen->member_phone,
										date("d-M-Y",strtotime($agen->member_joindate)),
										$agen->member_status
									));

									$no++;
								}
				

					   

		    });

		})->export('xls');
	}


	public function getExcelorder()
	{
		$b = Session::get('bulan');
		$t = Session::get('tahun');
        $bln = array('Januari' => 1, 'Februari' => 2, 'Maret' => 3, 'April' => 4, 'Mei' => 5, 'Juni' => 6, 'Juli' => 7, 'Agustus' => 8, 'September' => 9 , 'Oktober' => 10, 'November' => 11, 'Desember' => 12  );
		foreach($bln as $key => $val){ if($val == $b) $bulan = $key; };
		$name = "Daftar order";
		$title = "Daftar order bulan ".$bulan." ".$t;
		$no = 1;
		$orderlistorder = DB::table('sys_order')
                ->join('sys_customer', 'order_customer_id', '=', 'customer_id')
		  		->whereBetween('order_date', array($t.'-'.$b.'-01 00:00:00', $t.'-'.$b.'-31 23:59:59'))
                ->groupBy('order_id')
                ->orderBy('order_date', 'DESC')
                ->get();
		

		Excel::create('Laporan bulanan', function($excel) use($name,$orderlistorder,$no) {

		    $excel->sheet($name, function($sheet) use($orderlistorder,$no) {
		    					$sheet->row(1, array(
												 "No",
										         "Kode Order",
										         "Nama customer",
										         "Order",
										         "Jumlah",
										      	 "Status order"
									));
						    	foreach($orderlistorder as $orderlist){
								if($orderlist->order_name=='idcard'){
						          $type = DB::table('sys_order_idcard')
						                    ->where('order_idcard_order_id','=',$orderlist->order_id)
						                    ->first();
						          $ordername = 'ID Card ('.$type->order_idcard_quality_name.')';
						        }
						        else if($orderlist->order_name=='packaging'){
						          $type = DB::table('sys_order_packaging')
						                    ->where('order_packaging_order_id','=',$orderlist->order_id)
						                    ->first();
						          $ordername = 'Packaging ('.$type->order_packaging_type.')';
						        }
						        else if($orderlist->order_name=='brochure'){
						          $type = DB::table('sys_order_brochure')
						                        ->where('order_brochure_order_id','=',$orderlist->order_id)
						                        ->first();
						          if($type->order_brochure_type=="brochure"){
						          $ordername = 'Brosur (Brosur)';
						          }else if($type->order_brochure_type=="envelope"){
						          $ordername = 'Brosur (Amplop)';
						          }else if($type->order_brochure_type=="flayer"){
						          $ordername = 'Brosur (Flayer)';
						          }else if($type->order_brochure_type=="letterhead"){
						          $ordername = 'Brosur (Kop Surat)';
						          }
						        }
						        else if($orderlist->order_name=='nota'){
						          $type = DB::table('sys_order_bill')
						                        ->where('order_bill_order_id','=',$orderlist->order_id)
						                        ->first();
						          $ordername = $type->order_bill_name;
						        }
						        else if($orderlist->order_name=='tshirt'){
						           $type = DB::table('sys_order_tshirt')
						                        ->join('ref_tshirt_detail','tshirt_detail_id','=','order_tshirt_tshirt_id')
						                        ->join('ref_tshirt','tshirt_id','=','tshirt_detail_tshirt_id')
						                        ->where('order_tshirt_order_id','=',$orderlist->order_id)
						                        ->first();
						          $ordername = 'T-Shirt ('.$type->tshirt_type_of_material.')';
						        }
						        
									$sheet->row($no+1, array(
											 $no,
									         $orderlist->order_code,
									         $orderlist->customer_name,
									         $ordername,
									         $orderlist->order_qty.' '.$orderlist->order_qty_unit,
									         $orderlist->order_status
									));

									$no++;
								}
				

					   

		    });

		})->export('xls');
	}


	public function getExcelstatus()
	{
		$b = Session::get('bulan');
		$t = Session::get('tahun');
        $bln = array('Januari' => 1, 'Februari' => 2, 'Maret' => 3, 'April' => 4, 'Mei' => 5, 'Juni' => 6, 'Juli' => 7, 'Agustus' => 8, 'September' => 9 , 'Oktober' => 10, 'November' => 11, 'Desember' => 12  );
		foreach($bln as $key => $val){ if($val == $b) $bulan = $key; };
		$name = "Daftar status order";
		$title = "Daftar status order bulan ".$bulan." ".$t;
		$no = 1;
		$orderlistorder = DB::table('sys_order')
                ->join('sys_customer', 'order_customer_id', '=', 'customer_id')
		  		->whereBetween('order_date', array($t.'-'.$b.'-01 00:00:00', $t.'-'.$b.'-31 23:59:59'))
                ->groupBy('order_id')
                ->orderBy('order_date', 'DESC')
                ->get();
		

		Excel::create('Laporan bulanan', function($excel) use($name,$orderlistorder,$no) {

		    $excel->sheet($name, function($sheet) use($orderlistorder,$no) {
		    					$sheet->row($no, array(
												 "No",
										         "Kode Order",
										         "Nama customer",
										         "Order",
										         "Jumlah",
										      	 "Status order"
									));
						    	foreach($orderlistorder as $orderlist){
						    		if($orderlist->order_name=='idcard'){
						          $type = DB::table('sys_order_idcard')
						                    ->where('order_idcard_order_id','=',$orderlist->order_id)
						                    ->first();
						          $ordername = 'ID Card ('.$type->order_idcard_quality_name.')';
						        }
						        else if($orderlist->order_name=='packaging'){
						          $type = DB::table('sys_order_packaging')
						                    ->where('order_packaging_order_id','=',$orderlist->order_id)
						                    ->first();
						          $ordername = 'Packaging ('.$type->order_packaging_type.')';
						        }
						        else if($orderlist->order_name=='brochure'){
						          $type = DB::table('sys_order_brochure')
						                        ->where('order_brochure_order_id','=',$orderlist->order_id)
						                        ->first();
						          if($type->order_brochure_type=="brochure"){
						          $ordername = 'Brosur (Brosur)';
						          }else if($type->order_brochure_type=="envelope"){
						          $ordername = 'Brosur (Amplop)';
						          }else if($type->order_brochure_type=="flayer"){
						          $ordername = 'Brosur (Flayer)';
						          }else if($type->order_brochure_type=="letterhead"){
						          $ordername = 'Brosur (Kop Surat)';
						          }
						        }
						        else if($orderlist->order_name=='nota'){
						          $type = DB::table('sys_order_bill')
						                        ->where('order_bill_order_id','=',$orderlist->order_id)
						                        ->first();
						          $ordername = $type->order_bill_name;
						        }
						        else if($orderlist->order_name=='tshirt'){
						           $type = DB::table('sys_order_tshirt')
						                        ->join('ref_tshirt_detail','tshirt_detail_id','=','order_tshirt_tshirt_id')
						                        ->join('ref_tshirt','tshirt_id','=','tshirt_detail_tshirt_id')
						                        ->where('order_tshirt_order_id','=',$orderlist->order_id)
						                        ->first();
						          $ordername = 'T-Shirt ('.$type->tshirt_type_of_material.')';
						        }
						         
									$sheet->row($no+1, array(
												 $no,
										         $orderlist->order_code,
										         $orderlist->customer_name,
										         $ordername,
										         $orderlist->order_qty.' '.$orderlist->order_qty_unit,
										      	 $orderlist->order_status
									));

									$no++;
								}
				

					   

		    });

		})->export('xls');
	}



	public function getExcelproduk()
	{
		$b = Session::get('bulan');
		$t = Session::get('tahun');
        $bln = array('Januari' => 1, 'Februari' => 2, 'Maret' => 3, 'April' => 4, 'Mei' => 5, 'Juni' => 6, 'Juli' => 7, 'Agustus' => 8, 'September' => 9 , 'Oktober' => 10, 'November' => 11, 'Desember' => 12  );
		foreach($bln as $key => $val){ if($val == $b) $bulan = $key; };
		$name = "Daftar produk dibuat";
		$title = "Daftar produk dibuat bulan ".$bulan." ".$t;
		$no = 1;
		$orderlistorder = DB::table('sys_order')
       			->join('sys_order_status','order_status_order_id','=','order_id')
                ->join('sys_customer', 'order_customer_id', '=', 'customer_id')
				->where('order_status','=','Delivered')
				->where('order_status_type','=','Delivered')
                ->whereBetween('order_status_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->groupBy('order_id')
                ->orderBy('order_status_date', 'DESC')
                ->get();
		

		Excel::create('Laporan bulanan', function($excel) use($name,$orderlistorder,$no) {

		    $excel->sheet($name, function($sheet) use($orderlistorder,$no) {
		    					$sheet->row(1, array(
												 "No",
										         "Kode Order",
										         "Nama customer",
										         "Order",
										         "Jumlah",
										      	 "Status order"
									));



						    	foreach($orderlistorder as $orderlist){
								if($orderlist->order_name=='idcard'){
						          $type = DB::table('sys_order_idcard')
						                    ->where('order_idcard_order_id','=',$orderlist->order_id)
						                    ->first();
						          $ordername = 'ID Card ('.$type->order_idcard_quality_name.')';
						        }
						        else if($orderlist->order_name=='packaging'){
						          $type = DB::table('sys_order_packaging')
						                    ->where('order_packaging_order_id','=',$orderlist->order_id)
						                    ->first();
						          $ordername = 'Packaging ('.$type->order_packaging_type.')';
						        }
						        else if($orderlist->order_name=='brochure'){
						          $type = DB::table('sys_order_brochure')
						                        ->where('order_brochure_order_id','=',$orderlist->order_id)
						                        ->first();
						          if($type->order_brochure_type=="brochure"){
						          $ordername = 'Brosur (Brosur)';
						          }else if($type->order_brochure_type=="envelope"){
						          $ordername = 'Brosur (Amplop)';
						          }else if($type->order_brochure_type=="flayer"){
						          $ordername = 'Brosur (Flayer)';
						          }else if($type->order_brochure_type=="letterhead"){
						          $ordername = 'Brosur (Kop Surat)';
						          }
						        }
						        else if($orderlist->order_name=='nota'){
						          $type = DB::table('sys_order_bill')
						                        ->where('order_bill_order_id','=',$orderlist->order_id)
						                        ->first();
						          $ordername = $type->order_bill_name;
						        }
						        else if($orderlist->order_name=='tshirt'){
						           $type = DB::table('sys_order_tshirt')
						                        ->join('ref_tshirt_detail','tshirt_detail_id','=','order_tshirt_tshirt_id')
						                        ->join('ref_tshirt','tshirt_id','=','tshirt_detail_tshirt_id')
						                        ->where('order_tshirt_order_id','=',$orderlist->order_id)
						                        ->first();
						          $ordername = 'T-Shirt ('.$type->tshirt_type_of_material.')';
						        }
						        
									$sheet->row($no+1, array(
											 $no,
									         $orderlist->order_code,
									         $orderlist->customer_name,
									         $ordername,
									         $orderlist->order_qty.' '.$orderlist->order_qty_unit,
									         $orderlist->order_status
									));

									$no++;
								}
				

					   

		    });

		})->export('xls');
	}

	public function getExcelbonus()
	{	
		$b = Session::get('bulan');
		$t = Session::get('tahun');
        $bln = array('Januari' => 1, 'Februari' => 2, 'Maret' => 3, 'April' => 4, 'Mei' => 5, 'Juni' => 6, 'Juli' => 7, 'Agustus' => 8, 'September' => 9 , 'Oktober' => 10, 'November' => 11, 'Desember' => 12 );
		foreach($bln as $key => $val){ if($val == $b) $bulan = $key; };
		$name = "Daftar agen";
		$title = "Daftar agen bulan ".$bulan." ".$t;
		$no = 1;
		$datalogagen = DB::table('sys_bonus_log')
                ->join('sys_member', 'member_agen_id', '=', 'bonus_log_agen_id')
                ->join('sys_agen', 'member_agen_id', '=', 'agen_id')
                ->whereBetween('bonus_log_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->get();

		Excel::create('Laporan bulanan', function($excel) use($name,$datalogagen,$no) {

		    $excel->sheet($name, function($sheet) use($datalogagen,$no) {
		    						$sheet->row(1, array(
												 "No",
										         "Kode agen",
										         "Nama agen",
										         "Referal",
										         "Tanggal transaksi",
										      	 "Bonus order",
										      	 "Bonus referal",
										      	 "Status"
									));
						    	foreach($datalogagen as $agen){
									$refc = DB::table('sys_agen')
						                  ->where('agen_id',$agen->agen_sponsor_id) 
						                  ->count();
						            $ref = DB::table('sys_agen')
						                  ->where('agen_id',$agen->agen_sponsor_id) 
						                  ->first();
						            if($refc==0){
						                $code = 'Perusahaan';
						            }else{
						                $code = $ref->agen_code;
						            }
									
									$sheet->row($no+1, array(
											$no,
							                $agen->agen_code,
							                $agen->member_name,
							                $code,
							                date("d-M-Y",strtotime($agen->bonus_log_date)),
							                $agen->bonus_log_order,
							                $agen->bonus_log_referal,
							                $agen->member_status
									));

									$no++;
								}
				

					   

		    });

		})->export('xls');
	}


}